﻿Imports System.Data.OleDb
Imports System.Xml.Serialization

Public Class ClsExcelTextOLEDB
    Implements IDisposable

    Dim strSheetName As String
    Dim blnMixedData As Boolean
    Dim blnHeaders As Boolean
    Dim strFilename As String
    Dim strSheetRange As String
    Dim _ColumnNames As List(Of String)

    Private OConn As OleDbConnection
    Private OCmd As OleDbCommand
    Private OCmdSelect As OleDbCommand
    Dim _FileType As ImportFileType = ImportFileType.Excel
    Dim _TextFileOpt As TextFileOptions = TextFileOptions.CsvDelimited
    Dim _StrDelimiter As String

    Public Property Filename() As String
        Get
            Return strFilename
        End Get
        Set(ByVal value As String)
            If System.IO.File.Exists(value) Then
                strFilename = value
            End If
        End Set
    End Property


    Public Property IncludeHeaders() As Boolean
        Get
            Return blnHeaders
        End Get
        Set(ByVal value As Boolean)
            blnHeaders = value
        End Set
    End Property


    Public Property MixedData() As Boolean
        Get
            Return blnMixedData
        End Get
        Set(ByVal value As Boolean)
            blnMixedData = value
        End Set
    End Property


    Public Property CurrentSheetName() As String
        Get
            Return strSheetName
        End Get
        Set(ByVal value As String)
            strSheetName = value
        End Set
    End Property


    Public Property SheetRange() As String
        Get
            Return strSheetRange
        End Get
        Set(ByVal value As String)
            strSheetRange = value
        End Set
    End Property

    <XmlIgnore()> _
    Public Property ColumnNames As List(Of String)
        Get
            Return _ColumnNames
        End Get
        Set(ByVal value As List(Of String))
            _ColumnNames = value
        End Set
    End Property

    Public Property ConnectionType As ImportFileType
        Get
            Return _FileType
        End Get
        Set(ByVal value As ImportFileType)
            _FileType = value
        End Set
    End Property

    Public Property TextFileType As TextFileOptions
        Get
            Return _TextFileOpt
        End Get
        Set(ByVal value As TextFileOptions)
            _TextFileOpt = value
        End Set
    End Property

    Public Property Delimitter As String
        Get
            Return _StrDelimiter
        End Get
        Set(ByVal value As String)
            If value.Length = 1 Then
                _StrDelimiter = value
            Else
                Throw New Exception("Not a valid Value for a Delimiter,it Should be a one letter")
            End If
        End Set
    End Property

    <XmlIgnore()>
    Public ReadOnly Property ConnectionString() As String
        Get
            Return "Provider=Microsoft.Jet.OLEDB.4.0;" &
                "Data Source=" & strFilename & ";" &
                "Extended Properties=" & Convert.ToChar(34).ToString() &
                ConnectionStringOptions() & Convert.ToChar(34).ToString()
        End Get
    End Property

    <XmlIgnore()>
    Public ReadOnly Property GetExtension As String
        Get
            Select Case Me._FileType
                Case ImportFileType.Excel
                    Return "*.xls(97/2000/2003 Formats) |*.xls"
                Case ImportFileType.TextFile
                    Return "*.txt(text Files |*.txt|*.csv(Comma seperated Files)|*.csv"
                Case Else
                    Return "*.*(All Files)|*.*"
            End Select

        End Get
    End Property

    Private Function ConnectionStringOptions() As String
        Dim strOpts As String = ""
        If Me.ConnectionType = ImportFileType.Excel Then
            strOpts += "Excel 8.0;"
            If Me.MixedData = True Then
                strOpts += "Imex=1;"
            Else
                strOpts += "Imex=2;"
            End If
            strOpts += "TypeGuessRows=0;ImportMixedTypes=Text;"
        Else
            strOpts += "text;"
        End If
        If Me.IncludeHeaders = True Then
            strOpts += "HDR=Yes;"
        Else
            strOpts += "HDR=No;"
        End If
        If Me.ConnectionType = ImportFileType.TextFile Then

            Select Case _TextFileOpt
                Case TextFileOptions.CsvDelimited
                    strOpts += "FMT=CsvDelimited;"
                Case TextFileOptions.TabDelimited
                    strOpts += "FMT=TabDelimited;"
                Case TextFileOptions.FixedLength
                    strOpts += "FMT=FixedLength;"
                Case TextFileOptions.CustomDelimited
                    strOpts += "FMT=Delimited(" + _StrDelimiter + ");"
            End Select

        End If
        Return strOpts
    End Function

    Public Function GetExcelSheetNames() As [String]()
        Dim ExcelSheets(1) As String
        Try
            Using OConn As New OleDbConnection(ConnectionString)
                OConn.Open()
                ' Get the data table containing the schema
                Using dt As System.Data.DataTable = OConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
                    If dt Is Nothing Then
                        Return ExcelSheets
                    End If
                    ReDim ExcelSheets(dt.Rows.Count - 1)
                    Dim i As Integer = 0

                    ''Add the sheet name to the string array.
                    'For Each row As DataRow In dt.Rows
                    '    Dim strSheetTableName As String = row("TABLE_NAME").ToString()
                    '    excelSheets(i) = strSheetTableName
                    '    '.Substring(0,strSheetTableName.Length-1); 
                    '    i += 1
                    'Next
                    ExcelSheets = (From r As DataRow In dt.Rows Where r("Table_Name").ToString.EndsWith("_") = False Select CStr(r.Item("TABLE_NAME")).Replace("'", "")).ToArray()

                    Return ExcelSheets
                End Using
            End Using
        Catch generatedExceptionName As Exception
            Return ExcelSheets
        End Try
    End Function

    <System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")> _
    Public Function GetColumnNames(Optional ByVal Tablename As String = "") As IEnumerable(Of String)
        Dim cols As New List(Of String)
        If Tablename = "" Then
            Tablename = Me.CurrentSheetName
        End If
        If Tablename = "" Then Return cols
        Try
            Using con As New OleDbConnection(Me.ConnectionString)
                con.Open()
                Dim strsql As String = "SELECT * FROM " + Me.GetValidSqlName(Tablename) + " Where 1=2"
                Using Adp As New OleDbDataAdapter(strsql, con)
                    Using Tbl As New DataTable
                        Adp.Fill(Tbl)
                        cols = (From cl As DataColumn In Tbl.Columns Select cl.ColumnName).ToList
                    End Using
                End Using
            End Using
        Catch ex As Exception
            Return cols
        End Try
        Return cols
    End Function

    Public Function GetSourceColumns(ByVal Tablename As String) As IEnumerable(Of DataColumn)
        Dim cols As New List(Of DataColumn)
        Using con As New OleDbConnection(Me.ConnectionString)
            con.Open()
            Dim strsql As String = "SELECT * FROM " + Me.GetValidSqlName(Tablename) + " Where 1=2"
            Using Adp As New OleDbDataAdapter(strsql, con)
                Using Tbl As New DataTable
                    Adp.Fill(Tbl)
                    Tbl.Columns(0).Unique = True
                    cols = (From cl As DataColumn In Tbl.Columns Select cl).ToList
                End Using
            End Using
        End Using
        Return cols
    End Function

    Public Function GetData(Optional ByVal Tablename As String = "") As DataTable
        If Tablename = "" Then
            Tablename = Me.CurrentSheetName
        End If
        Dim Tbl As New DataTable
        Try
            Using con As New OleDbConnection(Me.ConnectionString)
                con.Open()
                Dim strsql As String = "SELECT * FROM " + Me.GetValidSqlName(Tablename) + " "
                Using Adp As New OleDbDataAdapter(strsql, con)
                    Adp.Fill(Tbl)
                End Using
            End Using
        Catch ex As Exception
            Debug.Print(ex.Message)
        End Try
        Return Tbl
    End Function


    Public Function GetDataByCriteria(Optional ByVal Tablename As String = "", Optional Criteria As String = "", Optional Orderby As String = "") As DataTable
        If Tablename = "" Then
            Tablename = Me.CurrentSheetName
        End If
        Dim Tbl As New DataTable
        Try
            Using con As New OleDbConnection(Me.ConnectionString)
                con.Open()
                Dim strsql As String = "SELECT * FROM " + Me.GetValidSqlName(Tablename) + " "
                If Criteria.Trim.Length > 0 Then
                    Criteria = IIf(Criteria.ToUpper.Contains(" WHERE "), Criteria, " WHERE " + Criteria)
                End If
                If Orderby.Trim.Length > 0 Then
                    Orderby = IIf(Orderby.ToUpper.Contains(" ORDER ") And Orderby.ToUpper.Contains(" BY "), Orderby, " ORDER BY " + Orderby)
                End If
                Using Adp As New OleDbDataAdapter(strsql + Criteria, con)
                    Adp.Fill(Tbl)
                End Using
            End Using
            Return Tbl
        Catch ex As Exception
            General.ShowErrorMessage(ex)
            Return Nothing
        End Try
    End Function

    Public Function GetDataForQuery(ByVal Query As String, Optional DataTableName As String = "DTable") As DataTable
        Dim Tbl As New DataTable
        Try
            Using con As New OleDbConnection(Me.ConnectionString)
                con.Open()
                Using Adp As New OleDbDataAdapter(Query, con)
                    Adp.Fill(Tbl)
                End Using
            End Using
            Tbl.TableName = DataTableName
            Return Tbl
        Catch ex As Exception
            General.ShowErrorMessage(ex)
            Return Nothing
        End Try
    End Function

    Public Function GetScalarValue(ByVal Query As String) As Object
        Try
            Dim obj As Object
            Using con As New OleDbConnection(Me.ConnectionString)
                con.Open()
                Using cmd As New OleDbCommand(Query, con)
                    obj = cmd.ExecuteScalar()
                End Using
            End Using
            Return obj
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function GetValidSqlName(ByVal TableName As String)
        Return " [" + TableName + "] "
    End Function


#Region "IDisposable Support"
    Private disposedValue As Boolean ' To detect redundant calls

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                ' : dispose managed state (managed objects).
            End If

            ' : free unmanaged resources (unmanaged objects) and override Finalize() below.
            ' : set large fields to null.
            If OCmd IsNot Nothing Then OCmd.Dispose()
            If OCmdSelect IsNot Nothing Then OCmdSelect.Dispose()
            If OConn IsNot Nothing Then OConn.Dispose()

            _ColumnNames = Nothing
        End If

        Me.disposedValue = True
    End Sub



    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.

        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub





#End Region


End Class
